package com.test.dao;

import com.test.entity.CompAllInfoDto;
import com.test.entity.StuAllInfoDto;
import com.test.entity.TimeTableDto;

public class CompOperatProvider {

    public String selectMyAttentStu(String  compName){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "\ttssi.stu_class,\n" +
                "\ttssi.stu_code,\n" +
                "\ttssi.stu_department,\n" +
                "\ttssi.stu_education,\n" +
                "\ttssi.stu_inTime,\n" +
                "\ttssi.stu_inway,\n" +
                "\ttssi.stu_outTime,\n" +
                "\ttssi.stu_profession,\n" +
                "\ttssi.stu_schoolname,\n" +
                "\ttsbi.stu_address,\n" +
                "\ttsbi.stu_city,\n" +
                "\ttsbi.stu_counselor,\n" +
                "\ttsbi.stu_county,\n" +
                "\ttsbi.stu_ec,\n" +
                "\ttsbi.stu_name,\n" +
                "\ttsbi.stu_ectel,\n" +
                "\ttsbi.stu_email,\n" +
                "\ttsbi.stu_fromaddress,\n" +
                "\ttsbi.stu_fromcity,\n" +
                "\ttsbi.stu_fromprovince,\n" +
                "\ttsbi.stu_idcard,\n" +
                "\ttsbi.stu_sex,\n" +
                "\ttsbi.stu_tel,\n" +
                "\ttu.u_status,\n" +
                "\ttu.u_acount,\n" +
                "tsbi.stu_qq,\n" +
                "\ttsji.stu_compaddress,\n" +
                "\ttsji.stu_compagreement,\n" +
                "\ttsji.stu_compcity,\n" +
                "\ttsji.stu_compcode,\n" +
                "\ttsji.stu_compcounty,\n" +
                "\ttsji.stu_compjob,\n" +
                "\ttsji.stu_compjobguide,\n" +
                "\ttsji.stu_compjobstatus,\n" +
                "\ttsji.stu_compkind,\n" +
                "\ttsji.stu_compname,\n" +
                "\ttsji.stu_compnature,\n" +
                "\ttsji.stu_updateagreement\n" +
                "FROM\n" +
                "\ttable_user tu\n" +
                "LEFT JOIN table_stubase_info tsbi ON tu.u_acount = tsbi.u_acount\n" +
                "LEFT JOIN table_stustatus_info tssi ON tu.u_acount = tssi.u_acount\n" +
                "LEFT JOIN table_stujob_info tsji ON tu.u_acount = tsji.u_acount\n" +
                "RIGHT JOIN table_stuattentcomp_info tstti ON tstti.u_acount = tu.u_acount\n" +
                "WHERE\n" +
                "tssi.stu_code IS NOT NULL\n" +
                "AND tsbi.stu_name IS NOT NULL");

        if (compName!=null&&!"".equals(compName)){
            sql.append(" and tstti.comp_name ='"+compName+"'");
        }else{
            sql.append(" and tstti.comp_name = '"+0+"'");
        }

        return sql.toString();
    }



    public String selectCompNameByAcount(String  acount){
        StringBuilder sql = new StringBuilder();
        sql.append(" select comp_name from table_compbase_info where 1=1");
        if (acount!=null&&!"".equals(acount)){
            sql.append(" and u_acount = '"+acount+"'");
        }else{
            sql.append(" and u_acount = '"+0+"'");
        }
        sql.append(" GROUP BY u_acount");
        return sql.toString();
    }



    public String selectAttentByacount(String  acount){
        StringBuilder sql = new StringBuilder();
        sql.append("select stu_name,comp_name  from table_compattentstu_info where 1=1");
        if(acount!=null&&!"".equals(acount)){
            sql.append("  and u_acount = '"+acount+"'");
        }else{
            sql.append("  and u_acount = "+0+"");
        }
        sql.append(" group by u_acount");
        return sql.toString();
    }

    public String compAttentStu(String acount,String compName,String stu_name){
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into table_compattentstu_info(stu_name,comp_name,attent_time,u_acount) values( ");


        if(stu_name!=null&&!"".equals(stu_name)){
            sql.append("'"+stu_name+"',");
        }else{
            sql.append("'"+0+"',");
        }

        if(compName!=null&&!"".equals(compName)){
            sql.append("'"+compName+"',");
        }else{
            sql.append("'"+0+"',");
        }
        sql.append("now(),");

        if(acount!=null&&!"".equals(acount)){
            sql.append("'"+acount+"'");
        }else{
            sql.append("'"+0+"'");
        }
        sql.append(")");
        return sql.toString();
    }


    public String selectCompAttentStu(String comp_acount){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "\ttssi.stu_class,\n" +
                "\ttssi.stu_code,\n" +
                "\ttssi.stu_department,\n" +
                "\ttssi.stu_education,\n" +
                "\ttssi.stu_inTime,\n" +
                "\ttssi.stu_inway,\n" +
                "\ttssi.stu_outTime,\n" +
                "\ttssi.stu_profession,\n" +
                "\ttssi.stu_schoolname,\n" +
                "\ttsbi.stu_address,\n" +
                "\ttsbi.stu_city,\n" +
                "\ttsbi.stu_counselor,\n" +
                "\ttsbi.stu_county,\n" +
                "\ttsbi.stu_ec,\n" +
                "\ttsbi.stu_name,\n" +
                "\ttsbi.stu_ectel,\n" +
                "\ttsbi.stu_email,\n" +
                "\ttsbi.stu_fromaddress,\n" +
                "\ttsbi.stu_fromcity,\n" +
                "\ttsbi.stu_fromprovince,\n" +
                "\ttsbi.stu_idcard,\n" +
                "\ttsbi.stu_sex,\n" +
                "\ttsbi.stu_tel,\n" +
                "\ttu.u_status,\n" +
                "\ttu.u_acount,\n" +
                "  tsbi.stu_qq,\n" +
                "\ttsji.stu_compaddress,\n" +
                "\ttsji.stu_compagreement,\n" +
                "\ttsji.stu_compcity,\n" +
                "\ttsji.stu_compcode,\n" +
                "\ttsji.stu_compcounty,\n" +
                "\ttsji.stu_compjob,\n" +
                "\ttsji.stu_compjobguide,\n" +
                "\ttsji.stu_compjobstatus,\n" +
                "\ttsji.stu_compkind,\n" +
                "\ttsji.stu_compname,\n" +
                "\ttsji.stu_compnature,\n" +
                "\ttsji.stu_updateagreement,\n" +
                "tctti.u_acount\n" +
                "FROM\n" +
                "\ttable_user tu\n" +
                "LEFT JOIN table_stubase_info tsbi ON tu.u_acount = tsbi.u_acount\n" +
                "LEFT JOIN table_stustatus_info tssi ON tu.u_acount = tssi.u_acount\n" +
                "LEFT JOIN table_stujob_info tsji ON tu.u_acount = tsji.u_acount\n" +
                "RIGHT JOIN table_compattentstu_info tctti on tctti.stu_name = tsbi.stu_name");


        return sql.toString();

}

    public String selectStuOnline(String comp_acount){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "                tu.u_acount,\n" +
                "                tsbi.stu_name,\n" +
                "                tsbi.stu_tel,\n" +
                "                tssi.stu_resume_address,\n" +
                "                tssi.stu_resume_awards,\n" +
                "                tssi.stu_resume_community_description,\n" +
                "                tssi.stu_resume_community_intime,\n" +
                "                tssi.stu_resume_community_name,\n" +
                "                tssi.stu_resume_community_outtime,\n" +
                "                tssi.stu_resume_community_position,\n" +
                "                tssi.stu_resume_salary,\n" +
                "                tssi.stu_resume_search,\n" +
                "                tssi.stu_resume_skills,\n" +
                "                tcpi.attent_status,\n" +
                "                tcpi.wangshenjob\n" +
                "                FROM\n" +
                "                table_user tu\n" +
                "                LEFT JOIN table_sturesume_info tssi ON tu.u_acount = tssi.u_acount\n" +
                "                LEFT JOIN table_stubase_info tsbi ON tsbi.u_acount = tu.u_acount\n" +
                "                right JOIN table_compattentstu_info tcpi on tu.u_acount = tcpi.u_acount\n" +
                "                WHERE\n" +
                "                tssi.stu_resume_salary IS NOT NULL\n" +
                "                AND tssi.stu_resume_skills IS NOT NULL\n" +
                "                AND tssi.stu_resume_search IS NOT NULL");

        if(comp_acount!=null&&!"".equals(comp_acount)){
            sql.append(" and tcpi.comp_acount = '"+comp_acount+"'");
        }else{
            sql.append(" and tcpi.comp_acount = '"+0+"'");
        }
//        sql.append(" GROUP BY tcpi.u_acount");
        return sql.toString();
    }


    public String agreeOnlineApplication(String acount,String stu_acount,String wangshenjob){
        StringBuilder sql = new StringBuilder();
        sql.append(" update table_compattentstu_info set attent_status = 1 where 1=1");

        if(acount!=null&&!"".equals(acount)){
            sql.append(" and comp_acount = '"+acount+"'");
        }else{
            sql.append(" and comp_acount = '"+0+"'");
        }
        if(stu_acount!=null&&!"".equals(stu_acount)){
            sql.append(" and u_acount = '"+stu_acount+"'");
        }else{
            sql.append(" and u_acount = '"+0+"'");
        }
        if(wangshenjob!=null&&!"".equals(wangshenjob)){
            sql.append(" and wangshenjob = '"+wangshenjob+"'");
        }else{
            sql.append(" and wangshenjob = '"+0+"'");
        }
        return sql.toString();
    }


    public String compyuyuebishi(String acount,String stu_acount,String wangshenjob){
        StringBuilder sql = new StringBuilder();
        sql.append(" update table_compattentstu_info set attent_status = 3 where 1=1");

        if(acount!=null&&!"".equals(acount)){
            sql.append(" and comp_acount = '"+acount+"'");
        }else{
            sql.append(" and comp_acount = '"+0+"'");
        }
        if(stu_acount!=null&&!"".equals(stu_acount)){
            sql.append(" and u_acount = '"+stu_acount+"'");
        }else{
            sql.append(" and u_acount = '"+0+"'");
        }
        if(wangshenjob!=null&&!"".equals(wangshenjob)){
            sql.append(" and wangshenjob = '"+wangshenjob+"'");
        }else{
            sql.append(" and wangshenjob = '"+0+"'");
        }
        return sql.toString();
    }

    public String compyuyuemianshi(String acount,String stu_acount,String wangshenjob){
        StringBuilder sql = new StringBuilder();
        sql.append(" update table_compattentstu_info set attent_status = 5 where 1=1");

        if(acount!=null&&!"".equals(acount)){
            sql.append(" and comp_acount = '"+acount+"'");
        }else{
            sql.append(" and comp_acount = '"+0+"'");
        }
        if(stu_acount!=null&&!"".equals(stu_acount)){
            sql.append(" and u_acount = '"+stu_acount+"'");
        }else{
            sql.append(" and u_acount = '"+0+"'");
        }
        if(wangshenjob!=null&&!"".equals(wangshenjob)){
            sql.append(" and wangshenjob = '"+wangshenjob+"'");
        }else{
            sql.append(" and wangshenjob = '"+0+"'");
        }
        return sql.toString();
    }


    public String sendOffer(String acount,String stu_acount,String wangshenjob){
        StringBuilder sql = new StringBuilder();
        sql.append(" update table_compattentstu_info set attent_status = 7 where 1=1");

        if(acount!=null&&!"".equals(acount)){
            sql.append(" and comp_acount = '"+acount+"'");
        }else{
            sql.append(" and comp_acount = '"+0+"'");
        }
        if(stu_acount!=null&&!"".equals(stu_acount)){
            sql.append(" and u_acount = '"+stu_acount+"'");
        }else{
            sql.append(" and u_acount = '"+0+"'");
        }
        if(wangshenjob!=null&&!"".equals(wangshenjob)){
            sql.append(" and wangshenjob = '"+wangshenjob+"'");
        }else{
            sql.append(" and wangshenjob = '"+0+"'");
        }
        return sql.toString();
    }


    public String disOnlineApplication(String acount,String stu_acount,String wangshenjob){
        StringBuilder sql = new StringBuilder();
        sql.append(" update table_compattentstu_info set attent_status = 2 where 1=1");

        if(acount!=null&&!"".equals(acount)){
            sql.append(" and comp_acount = '"+acount+"'");
        }else{
            sql.append(" and comp_acount = '"+0+"'");
        }
        if(stu_acount!=null&&!"".equals(stu_acount)){
            sql.append(" and u_acount = '"+stu_acount+"'");
        }else{
            sql.append(" and u_acount = '"+0+"'");
        }
        if(wangshenjob!=null&&!"".equals(wangshenjob)){
            sql.append(" and wangshenjob = '"+wangshenjob+"'");
        }else{
            sql.append(" and wangshenjob = '"+0+"'");

        }
        return sql.toString();
    }

    public String rebackOnlineApplication(String acount,String stu_acount,String wangshenjob){
        StringBuilder sql = new StringBuilder();
        sql.append(" update table_compattentstu_info set attent_status = 0 where 1=1");

        if(acount!=null&&!"".equals(acount)){
            sql.append(" and comp_acount = '"+acount+"'");
        }else{
            sql.append(" and comp_acount = '"+0+"'");
        }
        if(stu_acount!=null&&!"".equals(stu_acount)){
            sql.append(" and u_acount = '"+stu_acount+"'");
        }else{
            sql.append(" and u_acount = '"+0+"'");
        }
        if(wangshenjob!=null&&!"".equals(wangshenjob)){
            sql.append(" and wangshenjob = '"+wangshenjob+"'");
        }else{
            sql.append(" and wangshenjob = '"+0+"'");
        }
        return sql.toString();
    }

}


